<?php

//多表查询

//先建一个数据库
create database M_blue default character set utf8;

//选择操作的数据库M_blue
use M_blue;

//在数据库里面创建表   学生主表
create table student(
    id  int(5) not null primary key AUTO_INCREMENT,
    name varchar(30) not null,
    stu_id int(6) unsigned zerofill not null COMMENT '学号',
    sex enum('男','女','保密') default '男',
    age int(3) not null,
    class_id int(3) COMMENT '班级'
);

//插入数据
insert into student(name,stu_id,sex,age,class_id) values('小李',102184,'男',18,1),('小白',102185,'女',20,1),('小潘',102186,'女',28,2),('小邓',102187,'男',17,3),('小黑',102188,'男',25,3);

//再建一个学生扩展表
//zerofill 只能放前面  不能在not null后面
create table student_ext(
    id int(5) not null primary key AUTO_INCREMENT,
    sid  int(5) zerofill not null COMMENT '学生id',  
    father varchar(30) default NULL COMMENT '父亲',
    adress varchar(40) default NULL
);


//插入数据
insert into student_ext(sid,father,adress) values('1','小李爸','北京'),('2','小白爸','上海'),('3','小潘爸','广西'),('4','小邓爸','广州'),('5','小黑爸','深圳');

//把两个表内联
//student t1  给student取个别名t1  inner join连接   on 以什么为条件连接
select * from student t1 inner join student_ext t2 on t1.id = t2.sid;
//结果如下
+----+------+--------+------+-----+----------+----+-------+--------+--------+
| id | name | stu_id | sex  | age | class_id | id | sid   | father | adress |
+----+------+--------+------+-----+----------+----+-------+--------+--------+
|  1 | 小李 | 102184 | 男   |  18 |        1 |  1 | 00001 | 小李爸 | 北京   |
|  2 | 小白 | 102185 | 女   |  20 |        1 |  2 | 00002 | 小白爸 | 上海   |
|  3 | 小潘 | 102186 | 女   |  28 |        2 |  3 | 00003 | 小潘爸 | 广西   |
|  4 | 小邓 | 102187 | 男   |  17 |        3 |  4 | 00004 | 小邓爸 | 广州   |
|  5 | 小黑 | 102188 | 男   |  25 |        3 |  5 | 00005 | 小黑爸 | 深圳   |
+----+------+--------+------+-----+----------+----+-------+--------+--------+

//找出相关小白的扩展信息  加个选择条件where
select * from student t1 inner join student_ext t2 on t1.id = t2.sid where t1.name='小白';
//结果如下
+----+------+--------+------+-----+----------+----+-------+--------+--------+
| id | name | stu_id | sex  | age | class_id | id | sid   | father | adress |
+----+------+--------+------+-----+----------+----+-------+--------+--------+
|  2 | 小白 | 102185 | 女   |  20 |        1 |  2 | 00002 | 小白爸 | 上海   |
+----+------+--------+------+-----+----------+----+-------+--------+--------+

//只想输出小白对应的父亲名字和地址
//select  中间这里是输出  from
select t1.name,t2.father,t2.adress from student t1 inner join student_ext t2 on t1.id = t2.sid where t1.name='小白';
//结果如下
+------+--------+--------+
| name | father | adress |
+------+--------+--------+
| 小白 | 小白爸 | 上海   |
+------+------ -+--------+


//再建一个班级表 _class
create table _class(
   id int(6) primary key AUTO_INCREMENT,
   classname varchar(30) COMMENT '班级名'
);

//插入数据
insert into _class(classname) values('PHP'),('JAVA'),('HTML');
//查询数据
+----+-----------+
| id | classname |
+----+-----------+
|  1 | PHP       |
|  2 | JAVA      |
|  3 | HTML      |
+----+-----------+

//创建一个考试科目成绩表  test
create table test(
   sid int(6) COMMENT '学生id',
   subject int(6) COMMENT '科目id',
   score int(3) COMMENT '考试成绩'
);

//插入数据
insert into test(sid,subject,score) values(1,1,90),(2,3,79),(3,2,65),(4,1,80),(5,3,40);
//查看数据
+------+---------+-------+
| sid  | subject | score |
+------+---------+-------+
|    1 |       1 |    90 |
|    2 |       3 |    79 |
|    3 |       2 |    65 |
|    4 |       1 |    80 |
|    5 |       3 |    40 |
+------+---------+-------+

//新建科目表 km_table
create table km_table(
  id int(6)primary key AUTO_INCREMENT,
  kname varchar(30) COMMENT '科目名'
);

//插入数据
insert into km_table(kname) values('数学'),('语文'),('英语');
//查看数据
+----+-------+
| id | kname |
+----+-------+
|  1 | 数学  |
|  2 | 语文  |
|  3 | 英语  |
+----+-------+


//要连学生 考试 科目表
 
//t1的id和t2的sid对应：学生id一一对应      t2的id和t3的id对应：哪个科目考了多少分
select * from student t1 left join test t2 on t1.id = t2.sid;
+----+------+--------+------+-----+----------+------+---------+-------+
| id | name | stu_id | sex  | age | class_id | sid  | subject | score |
+----+------+--------+------+-----+----------+------+---------+-------+
|  1 | 小李 | 102184 | 男   |  18 |        1 |    1 |       1 |    90 |
|  2 | 小白 | 102185 | 女   |  20 |        1 |    2 |       3 |    79 |
|  3 | 小潘 | 102186 | 女   |  28 |        2 |    3 |       2 |    65 |
|  4 | 小邓 | 102187 | 男   |  17 |        3 |    4 |       1 |    80 |
|  5 | 小黑 | 102188 | 男   |  25 |        3 |    5 |       3 |    40 |
+----+------+--------+------+-----+----------+------+---------+-------+

//科目和成绩对应表
select * from test t2 inner join km_table t3 on t2.subject=t3.id;
+------+---------+-------+----+-------+
| sid  | subject | score | id | kname |
+------+---------+-------+----+-------+
|    1 |       1 |    90 |  1 | 数学  |
|    2 |       3 |    79 |  3 | 英语  |
|    3 |       2 |    65 |  2 | 语文  |
|    4 |       1 |    80 |  1 | 数学  |
|    5 |       3 |    40 |  3 | 英语  |
+------+---------+-------+----+-------+

//学生、科目、成绩表对应关系
select * from student t1 inner join test t2 on t1.id = t2.sid inner join km_table t3 on t2.subject=t3.id;
+----+------+--------+------+-----+----------+------+---------+-------+----+-------+
| id | name | stu_id | sex  | age | class_id | sid  | subject | score | id | kname |
+----+------+--------+------+-----+----------+------+---------+-------+----+-------+
|  1 | 小李 | 102184 | 男   |  18 |        1 |    1 |       1 |    90 |  1 | 数学  |
|  2 | 小白 | 102185 | 女   |  20 |        1 |    2 |       3 |    79 |  3 | 英语  |
|  3 | 小潘 | 102186 | 女   |  28 |        2 |    3 |       2 |    65 |  2 | 语文  |
|  4 | 小邓 | 102187 | 男   |  17 |        3 |    4 |       1 |    80 |  1 | 数学  |
|  5 | 小黑 | 102188 | 男   |  25 |        3 |    5 |       3 |    40 |  3 | 英语  |
+----+------+--------+------+-----+----------+------+---------+-------+----+-------+

//想知道小潘的考试科目成绩
select t1.name,t3.kname,t2.score from student t1 inner join test t2 on t1.id = t2.sid inner join km_table t3 on t2.subject=t3.id where t1.name = '小潘';
+------+-------+-------+
| name | kname | score |
+------+-------+-------+
| 小潘 | 语文  |    65 |
+------+-------+-------+


//想知道PHP JAVA HTML 每个班的男女同学个数
//先连表
select * from student s inner join _class c  on s.id=c.id;
+----+------+--------+------+-----+----------+----+-----------+
| id | name | stu_id | sex  | age | class_id | id | classname |
+----+------+--------+------+-----+----------+----+-----------+
|  1 | 小李 | 102184 | 男   |  18 |        1 |  1 | PHP       |
|  2 | 小白 | 102185 | 女   |  20 |        1 |  2 | JAVA      |
|  3 | 小潘 | 102186 | 女   |  28 |        2 |  3 | HTML      |
+----+------+--------+------+-----+----------+----+-----------+

select * from student s left join _class c  on s.id=c.id;
+----+------+--------+------+-----+----------+------+-----------+
| id | name | stu_id | sex  | age | class_id | id   | classname |
+----+------+--------+------+-----+----------+------+-----------+
|  1 | 小李 | 102184 | 男   |  18 |        1 |    1 | PHP       |
|  2 | 小白 | 102185 | 女   |  20 |        1 |    2 | JAVA      |
|  3 | 小潘 | 102186 | 女   |  28 |        2 |    3 | HTML      |
|  4 | 小邓 | 102187 | 男   |  17 |        3 | NULL | NULL      |
|  5 | 小黑 | 102188 | 男   |  25 |        3 | NULL | NULL      |
+----+------+--------+------+-----+----------+------+-----------+
//按条件筛选
select s.name,c.classname from student s left join _class c  on s.id=c.id;
| name | classname |
+------+-----------+
| 小李 | PHP       |
| 小白 | JAVA      |
| 小潘 | HTML      |
| 小邓 | NULL      |
| 小黑 | NULL      |



















